package com.pengtu.gsj.dao.jsbz;

import java.util.List;

import org.springframework.stereotype.Repository;

import com.pengtu.gsj.dao.hibernate.HibernateDao;
import com.pengtu.gsj.entity.jsbz.RoadCycle;

/**
 * 
 * RoadCycleDao
 * 
 * 2017年7月24日 上午9:39:14
 * 
 * @version 1.0.0
 * 
 */
@Repository
public class RoadCycleDao extends HibernateDao<RoadCycle, String> {

	public List<RoadCycle> getRoadMatRecUtilizeYear(String startTime, String endTime) {
		String hql = "select new com.pengtu.gsj.entity.jsbz.RoadCycle(t.testYear) from RoadCycle t where t.delFlag='0' and t.testYear <="+endTime+"and t.testYear >="+startTime+" group by t.testYear order by t.testYear asc";
		return find(hql);
	}

	public List<RoadCycle> getCheckDataByTestYear(String testYear) {
		String hql = " SELECT new com.pengtu.gsj.entity.jsbz.RoadCycle"
				+ " (t.testYear as year,"
				+ " SUM(t.asphalMaterial),"
				+ " SUM(t.asphalLocalUse),"
				+ " SUM(t.asphalCycleUse),"
				+ " SUM(t.cementMaterial),"
				+ " SUM(t.cementLocalUse),"
				+ " SUM(t.cementCycleUse),"
				+ " (SUM(t.asphalLocalUse) + SUM(t.asphalCycleUse) + SUM(t.cementLocalUse) + SUM(t.cementCycleUse))/(SUM(t.asphalMaterial) + SUM(t.cementMaterial)) * 100,"
				+ " (SUM(t.asphalLocalUse) + SUM(t.asphalCycleUse))/(SUM(t.asphalMaterial)) * 100,"
				+ " (SUM(t.cementLocalUse) + SUM(t.cementCycleUse))/(SUM(t.cementMaterial)) * 100)"
				+ " FROM"
				+ " RoadCycle t"
				+ " WHERE "
				+ " t.delFlag = '0'"
				+ " AND t.testYear  = ? "
				+ " group by t.testYear order by testYear asc";
		return find(hql, testYear);
	}

	public List<RoadCycle> getCheckDataById(String itemNameId) {
		String hql = " SELECT new com.pengtu.gsj.entity.jsbz.RoadCycle"
				+ " (t.projectName,"
				+ " t.isUseCase,"
				+ " (t.asphalLocalUse + t.asphalCycleUse + t.cementLocalUse + t.cementCycleUse))"
				+ " FROM "
				+ " RoadCycle t"
				+ " WHERE"
				+ " t.delFlag = '0'"
				+ " AND t.id = " + "'"+itemNameId + "'";
		return find(hql);
	}
}
